package com.misc.tunewsreader;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteDB extends SQLiteOpenHelper {
	private SQLiteDatabase db;
	private String db_name;

	public SQLiteDB(Context context, String name) {
		super(context, name, null, 1);
		db_name = name;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int old_version, int new_version) {
	}

	@Override
	public synchronized void close() {
		if (db != null) {
			db.close();
			db = null;
		}
		super.close();
	}

	public boolean open() {
		try {
			db = getWritableDatabase();
			db.execSQL("CREATE TABLE IF NOT EXISTS "
					+ db_name
					+ " (number INT, newsgroup VARCHAR, full_post INT, first INT, last INT, read INT, header VARCHAR, article VARCHAR);");
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	public SQLiteDatabase getDb() {
		return db;
	}

	public void startTransaction() {
		db.setLockingEnabled(false);
		db.beginTransaction();
	}

	public void successfulTransaction() {
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public void unsuccessfulTransaction() {
		db.endTransaction();
	}

	public void deleteGroupInfo(NNTPGroup group) {
		db.delete(db_name, "number=0 AND newsgroup=?",
				new String[] { group.getName() });
	}

	public void deleteGroupPosts(NNTPGroup group) {
		db.delete(db_name, "number > 0 AND newsgroup = ?",
				new String[] { group.getName() });
	}

	public void deletePost(long m_id, String newsgroup) {
		db.delete(db_name, "number = ? AND newsgroup = ?", new String[] {
				String.valueOf(m_id), newsgroup });
	}

	public void saveGroupInfo(NNTPGroup group) {
		ContentValues values = new ContentValues();
		values.put("number", 0);
		values.put("full_post", 0);
		values.put("first", group.getFirstId());
		values.put("last", group.getLastId());
		values.put("newsgroup", group.getName());
		values.put("header", "");
		values.put("article", "");
		values.put("read", 0);
		if (getGroupInfo(group.getName()) == null) {
			db.insert(db_name, null, values);
		} else {
			db.update(db_name, values, "number=0 AND newsgroup=?",
					new String[] { group.getName() });
		}
	}

	public NNTPGroup getGroupInfo(String name) {
		Cursor cursor = db.query(db_name, new String[] { "first", "last",
				"newsgroup", "number" }, "newsgroup = ? AND number=0",
				new String[] { name }, null, null, null);

		cursor.moveToFirst();
		Log.d("NNTP", "Info available: " + cursor.getCount());

		if (cursor.getCount() == 1) {
			String group_info = cursor.getString(cursor
					.getColumnIndex("newsgroup"))
					+ " "
					+ cursor.getInt(cursor.getColumnIndex("last"))
					+ " "
					+ cursor.getInt(cursor.getColumnIndex("first"));
			Log.d("NNTP", "Group info: " + group_info);
			cursor.close();
			return new NNTPGroup(group_info);
		} else {
			cursor.close();
			return null;
		}
	}

	public void savePostHeader(String group, NNTPArticleHeader header) {
		ContentValues values = new ContentValues();
		values.put("newsgroup", group);
		values.put("number", header.getThreadId());
		values.put("full_post", 0);
		values.put("first", 0);
		values.put("last", 0);
		values.put("header", header.getAsString());
		values.put("read", 0);
		// values.put("article", "");

		Cursor res = db.query(db_name, new String[] { "number", "newsgroup" },
				"number = ? AND newsgroup = ?",
				new String[] { String.valueOf(header.getThreadId()), group },
				null, null, null);
		if (res.getCount() == 0) {
			db.insert(db_name, null, values);
		} else {
			db.update(
					db_name,
					values,
					"number = ? AND newsgroup = ?",
					new String[] { String.valueOf(header.getThreadId()), group });
		}
	}

	public boolean postExists(String group, long id) {
		boolean exists = false;
		Cursor cursor = db.query(db_name,
				new String[] { "newsgroup", "number" },
				"newsgroup=? AND number=?", null, null, null, null);
		if (cursor.getCount() > 0)
			exists = true;
		cursor.close();
		return exists;
	}

	public long postIdById(String id) {
		Cursor cursor = db.query(db_name,
				new String[] { "header", "number" },
				"header like ? AND number>0", new String[] {
						"%" + id + "%" }, null, null, null);
		long pid = -1;
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			pid = cursor.getInt(cursor.getColumnIndex("number"));
			cursor.close();
		}
		Log.d("SYNC", id + " -> " + pid);
		return pid;
	}
	
	public String groupById(String id) {
		Cursor cursor = db.query(db_name,
				new String[] { "header", "number", "newsgroup" },
				"header like ? AND number>0", new String[] {
						"%" + id + "%" }, null, null, null);
		String ng = "";
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			ng = cursor.getString(cursor.getColumnIndex("newsgroup"));
			cursor.close();
		}
		Log.d("SYNC", id + " is in " + ng);
		return ng;
	}

	public int getUnreadCount(String group) {
		Cursor cursor = db.query(db_name, new String[] { "newsgroup", "read",
				"number" }, "newsgroup=? AND number>0", new String[] { group },
				null, null, null);
		int cnt = cursor.getCount();
		cursor.close();

		cursor = db.query(db_name,
				new String[] { "newsgroup", "read", "number" },
				"newsgroup=? AND number>0 AND read=1", new String[] { group },
				null, null, null);
		cursor.moveToFirst();
		cnt -= cursor.getCount();
		cursor.close();

		Log.d("NNTP", "Group '" + group + "' has " + cnt + " unread posts");
		return cnt;
	}

	public List<NNTPArticleHeader> getPostHeaders(String group) {
		Cursor cursor = db.query(db_name, new String[] { "header", "newsgroup",
				"number", "read" }, "newsgroup = '" + group
				+ "' AND number > 0", null, null, null, "number");

		cursor.moveToFirst();
		List<NNTPArticleHeader> posts = new ArrayList<NNTPArticleHeader>();
		for (int i = 0; i < cursor.getCount(); i++) {
			String[] raw_header = cursor.getString(
					cursor.getColumnIndex("header")).split("\r\n");
			List<String> header = new ArrayList<String>();
			for (int j = 0; j < raw_header.length; j++) {
				header.add(raw_header[j]);
			}
			posts.add(new NNTPArticleHeader(header, cursor.getInt(cursor
					.getColumnIndex("number")), cursor.getInt(cursor
					.getColumnIndex("read"))));
			cursor.moveToNext();
		}
		cursor.close();
		return posts;
	}

	public String getPostBody(String group, long id) {
		Cursor cursor = db.query(db_name, new String[] { "number", "newsgroup",
				"full_post", "article" },
				"newsgroup=? AND number=? AND full_post=1", new String[] {
						group, String.valueOf(id) }, null, null, null);
		if (cursor.getCount() == 0)
			return "";
		cursor.moveToFirst();
		String body = cursor.getString(cursor.getColumnIndex("article"));
		cursor.close();
		return body;
	}

	public void savePostBody(String group, long id, String header, String data) {
		ContentValues values = new ContentValues();
		values.put("newsgroup", group);
		values.put("number", id);
		values.put("full_post", 1);
		values.put("article", data);
		values.put("header", header);
		if (getPostBody(group, id) == "") {
			db.insert(db_name, null, values);
		} else {
			db.update(db_name, values, "newsgroup=? AND number=?",
					new String[] { group, String.valueOf(id) });
		}
	}

	public void markPostRead(String group, long id, boolean is_read) {
		Log.d("NNTP", "mark #" + id + " as " + (is_read ? "read" : "unread"));
		ContentValues values = new ContentValues();
		values.put("newsgroup", group);
		values.put("number", id);
		values.put("read", is_read ? 1 : 0);
		db.update(db_name, values, "newsgroup = ? AND number = ?",
				new String[] { group, String.valueOf(id) });
	}

	public void markPostReadById(String id, boolean is_read) {
		Log.d("NNTP", "mark #" + id + " as " + (is_read ? "read" : "unread"));
		markPostRead(groupById(id), postIdById(id), is_read);
	}

	public void markGroupRead(String group, boolean is_read) {
		Log.d("NNTP", "mark '" + group + "' as "
				+ (is_read ? "read" : "unread"));
		ContentValues values = new ContentValues();
		values.put("newsgroup", group);
		values.put("read", is_read ? 1 : 0);
		db.update(db_name, values, "newsgroup=? AND number>0",
				new String[] { group });
	}

}
